import pandas, duckdb
from config import ini_op
from openpyxl import  load_workbook
import os
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection



# timenow = str(strftime("%Y%m%d%H%M%S"))
def run(dwbh, zbid, zb, kjnd, filepath, timenow):
  workbook = load_workbook(filepath, data_only=True)
  sheet = workbook.active
  kjnd = sheet['A2'].value[0:4]
  df1 = pandas.read_excel(filepath, skiprows=6, dtype=str)
  conn = get_connection()
  conn.register('temp_xmyeb_main', df1)
  zbnm = ini_op.getinivalue('zb_dict', 'zb' + zb)[0]
  conn.execute(f"""
    create or replace table t_xmyeb_main (组织名称 string,账簿名称 string,科目编号 string,科目名称 string,现金流项目编号 string,现金流项目名称 string,现金流项目全称 string,现金流项目级数 string,现金流项目助记码 string,现金流项目所属单位 string,期初方向 string,期初金额 string,借方 string,贷方 string,余额方向 string,余额金额 string);
    insert into t_xmyeb_main select * from temp_xmyeb_main;
    delete from t_xmyeb_main where 账簿名称 != '{zbnm}';
               """)

  conn.execute("""
               delete from T_API_FZNC where kjnd = ? and LEDGERBH = ? and ACCTITLECODE in (select 科目编号 from t_xmyeb_main group by 科目编号);
               """, [kjnd, zb])
  # conn.execute("""
  #       create or replace table t_yl_xmyeb as
  #              select
  #               uuid() as id,
  #               '"""+dwbh+"""' as 单位编号,
  #               '"""+zb+"""' as 账簿编号,
  #               '"""+kjnd+"""' as 会计年度,
  #               科目编号,
  #               科目名称,
  #               现金流项目编号 as 项目编号,
  #               现金流项目名称 as 项目名称,
  #               期初方向,
  #               期初金额,
  #               '"""+timenow+"""' as 更新时间
  #               from t_xmyeb_main;
  #   """)
  # print(zb)
  conn.execute("""
    insert into T_API_FZNC (ACCORGBH, LEDGER, LEDGERBH, ACCTITLECODE, SPECATEBH02, BALANCEDIR, BEGINBALANCEAMT, CURCUMDRAMT, CURCUMCRAMT, esun_api_update, kjnd)
              select
                '"""+dwbh+"""',
                '"""+zbid+"""',
                '"""+zb+"""',
                科目编号,
                现金流项目编号,
                case 期初方向
                when '平' then 0
                when '借' then 1
                when '贷' then 2
                end,
                CAST(REPLACE(期初金额, ',', '') AS DECIMAL(18, 2)),
                CAST(REPLACE(借方, ',', '') AS DECIMAL(18, 2)),
                CAST(REPLACE(贷方, ',', '') AS DECIMAL(18, 2)),
                '"""+timenow+"""',
                '"""+kjnd+"""'
               from t_xmyeb_main;
    delete from T_API_FZNC where ACCTITLECODE = '合计';
  """)

  conn.close()



# run('03047', '01051507', '2024', r'D:\code\app_report_back20241201\backend\项目余额表.xlsx', timenow)